package utils;


import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;

public class Handle extends Object {

    private String sql;
    private Connection connection;
    private java.sql.Statement statement;
    //private ResultSet results;
    private String ErrorMessage;

    private String dbName;
    private String userName;
    private String userPassword;

    private boolean connected = false;

    /**
     * Create a new handle to the CIMMS database using the default
     * database, user and password. Upon creating the handle, a
     * connection to the database is automatically made.  The constructor throws
     * ClassNotFoundException and SQLException associated with conecting
     * to the database.
     */

    public Handle(){

    }

    public Handle(String dbName, String userName, String password) {
        this.setDbName(dbName);
        this.setUserName(userName);
        this.setUserPassword(password);
        connect();
      }



    /**
     * Returns the name of the CIMMS database being accessed through
     * this handle.
     * @return A <code>String</code> representing the database name.
     */
    public String getDbName() {
        return dbName;
    }

    /**
     * Sets the name of the CIMMS database to be accessed through
     * this handle.
     * @param String representing the name of the CIMMS database.
     */
    public void setDbName (String value) {
        this.dbName = value;
    }

    /**
     * Returns the username being used to access the CIMMS database
     * though this handle.
     * @return A String representing the username.
     */
    public String getUserName() {
        return userName;
    }

    /**
     * Sets the username being used to access the CIMMS database
     * being accessed through this handle.
     * @param String representing the username.
     */
    public void setUserName (String value) {
        this.userName = value;
    }

    /**
     * Returns the password being used to access the CIMMS database
     * though this handle. Fo security issues, this is a private
     * method so that only this class can determine the password.
     * @return A String representing the password.
     */
    public String getUserPassword() {
        return userPassword;
    }

    /**
     * Sets the password being used to access the CIMMS database
     * being accessed through this handle.
     * @param String representing the password.
     */
    public void setUserPassword (String value) {
        this.userPassword = value;
    }

    public String getErrorMessage() {

        return ErrorMessage+" for sql line:\n"+sql;
    }

    /**
     * Initiates a connection to the CIMMS database.
     */
    public void connect(){
        try{
            Class.forName("com.mysql.jdbc.Driver");
        }catch(ClassNotFoundException msg){
            System.out.println("A connection could not be established: "+msg);
        }
        try{
            connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/"+this.getDbName(),this.getUserName(),this.getUserPassword());
        }catch(SQLException msg){
            System.out.println("A connection could not be established: "+msg);
        }
        connected = true;
    }



    /**
     * Runs an SQL query using and returns the results.
     */
    public ResultSet runSQLquery(String SQLquery){
        ResultSet results = null;
        try{
            statement = connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            results = statement.executeQuery(SQLquery);
        }catch (SQLException msg){
            System.out.println("error in getting result set for \n"+msg.toString()+"\n"+SQLquery);
        }
        return results;
    }

    /**
     * Runs an SQL update.
     */
    public void runSQLupdate(String SQLupdate){
        try{
            statement = connection.createStatement();
            statement.executeUpdate(SQLupdate);
        }catch (SQLException msg){
        	System.out.println("error performing update\n"+msg.toString()+"\n"+SQLupdate);
        }
    }

    /**
     * Closes the connection to the CIMMS database.
     */
    public void closeConnection(){
        try{
            if(statement!=null){
                statement.close();
            }
            connection.close();
        }catch (SQLException msg){
        	System.out.println("error closing connection");
        }
        connected = false;
    }

    public void createTable(String sql){
			this.runSQLupdate(sql);
	}

    public boolean isConnected(){
    	return connected;
    }


    public static Handle getConnectedHandle(String database){
        Handle h = new Handle();
        h.setDbName(database);
        h.setUserName("root");
        h.setUserPassword("jamie");
        h.connect();
        return h;
    }
}


